package com.example.demo01.Impl.mobileImpl;

import com.example.demo01.Dao.mobileDao.ServiceOrderDao;
import com.example.demo01.Entity.mobileEntity.ServiceOrder;
import com.example.demo01.Entity.mobileEntity.ServiceOrderSolved;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class ServiceOrderDaoImpl implements ServiceOrderDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 插入一条数据
     * @param serviceOrder
     * @return
     */
    @Override
    public int insert(ServiceOrder serviceOrder) {
        String sql="INSERT INTO serviceorders(clientusername,servicecontent,serviceaddress,lon,lat,ordertime)\n" +
                "VALUES(?,?,?,?,?,NOW())";
        return jdbcTemplate.update(sql,new Object[]{
                serviceOrder.getUsername(),
                serviceOrder.getServiceContent(),
                serviceOrder.getServiceAddress(),
                serviceOrder.getLon(),
                serviceOrder.getLat()
        });
    }

    /**
     * 获取所有服务订单数据
     * @return
     */
    @Override
    public List<ServiceOrder> selectAll(String username) {
        String sql="SELECT \n" +
                "\tclientusername as username,\n" +
                "\tservicecontent as scontent,\n" +
                "\tserviceaddress as saddress,\n" +
                "\tlon,\n" +
                "\tlat,\n" +
                "\tordertime as orderTime,\n" +
                "\torderstate as state\n" +
                "FROM serviceorders\n" +
                "WHERE clientusername=?\n" +
                "ORDER BY orderTime DESC";
        return jdbcTemplate.query(sql,new Object[]{username}, new RowMapper<ServiceOrder>() {
            @Nullable
            @Override
            public ServiceOrder mapRow(ResultSet resultSet, int i) throws SQLException {
                return new ServiceOrder(
                    resultSet.getString("username"),
                        resultSet.getString("scontent"),
                        resultSet.getString("saddress"),
                        resultSet.getFloat("lon"),
                        resultSet.getFloat("lat"),
                        resultSet.getString("orderTime"),
                        resultSet.getInt("state")
                );
            }
        });
    }

    /**
     * 获取所有详细订单数据
     * */
    @Override
    public List<ServiceOrderSolved> selectAllDetailByPageSize(Integer beginIndex,Integer offSize) {
        String sql="SELECT (@i:=@i+1) as id,A.* \n" +
                "\tFROM \n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tclientusername,\n" +
                "\t\t\t\tservicecontent,\n" +
                "\t\t\t\tserviceaddress,\n" +
                "\t\t\t\tlon,\n" +
                "\t\t\t\tlat,\n" +
                "\t\t\t\tordertime,\n" +
                "\t\t\t\torderstate,\n" +
                "\t\t\t\tadminaccount,\n" +
                "\t\t\t\tendtime\n" +
                "\t\t\tFROM \n" +
                "\t\t\t\tserviceorders INNER JOIN servicedeals ON serviceorders.id=servicedeals.orderid\n" +
                "\t\t\tORDER BY\n" +
                "\t\t\t\tordertime DESC) A,(select @i:=?) B\n" +
                "\t\t\t\tLIMIT ?,?";
        //执行SQL语句，采用id内连接，默认选择结果即为已经处理过的数据
        return jdbcTemplate.query(sql,new Object[]{beginIndex,beginIndex,offSize} ,new RowMapper<ServiceOrderSolved>() {
            @Nullable
            @Override
            public ServiceOrderSolved mapRow(ResultSet resultSet, int i) throws SQLException {
                return new ServiceOrderSolved(
                        resultSet.getInt("id"),
                        resultSet.getString("clientusername"),
                        resultSet.getString("servicecontent"),
                        resultSet.getString("serviceaddress"),
                        resultSet.getFloat("lon"),
                        resultSet.getFloat("lat"),
                        resultSet.getString("ordertime"),
                        resultSet.getInt("orderstate"),
                        resultSet.getString("adminaccount"),
                        resultSet.getString("endtime")
                );
            }
        });
    }

    /**
     * 获取详细订单数据——带id值的未处理订单
     * */
    @Override
    public List<ServiceOrder> selectAllWidthoutDeal(Integer beginIndex,Integer offSize) {
        String sql="SELECT\n" +
                "\t(@i:=@i+1) as id,\n" +
                "\tclientusername,\n" +
                "\tservicecontent,\n" +
                "\tserviceaddress,\n" +
                "\tlon,\n" +
                "\tlat,\n" +
                "\tordertime,\n" +
                "\torderstate\n" +
                "FROM serviceorders,(select @i:=?) B\n" +
                "WHERE orderstate=0\n" +
                "LIMIT ?,?";
        //执行SQL语句
        List<ServiceOrder> serviceOrderList = jdbcTemplate.query(sql,new Object[]{beginIndex,beginIndex,offSize}, new RowMapper<ServiceOrder>() {
            @Nullable
            @Override
            public ServiceOrder mapRow(ResultSet resultSet, int i) throws SQLException {
                return new ServiceOrder(
                        resultSet.getInt("id"),
                        resultSet.getString("clientusername"),
                        resultSet.getString("servicecontent"),
                        resultSet.getString("serviceaddress"),
                        resultSet.getFloat("lon"),
                        resultSet.getFloat("lat"),
                        resultSet.getString("ordertime"),
                        resultSet.getInt("orderstate")
                );
            }
        });
        return serviceOrderList;
    }

    /**
     * 获取分页的总页数
     * @return
     */
    @Override
    public int getTotalSize(Integer pageSize) {
        String sql="\tSELECT CEIL(COUNT(*)/?) as totalsize\n" +
                "\tFROM \n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tclientusername,\n" +
                "\t\t\t\tservicecontent,\n" +
                "\t\t\t\tserviceaddress,\n" +
                "\t\t\t\tlon,\n" +
                "\t\t\t\tlat,\n" +
                "\t\t\t\tordertime,\n" +
                "\t\t\t\torderstate,\n" +
                "\t\t\t\tadminaccount,\n" +
                "\t\t\t\tendtime\n" +
                "\t\t\tFROM \n" +
                "\t\t\t\tserviceorders INNER JOIN servicedeals ON serviceorders.id=servicedeals.orderid\n" +
                "\t\t\t\tORDER BY\n" +
                "\t\t\t\tordertime DESC) A,(select @i:=0) B";
        return jdbcTemplate.queryForObject(sql,new Object[]{pageSize},Integer.class);
    }

    /**
     * 获取未处理的消息的总页数
     * @param pageSize
     * @return
     */
    @Override
    public int getTotalSizeOfUnSolved(Integer pageSize) {
        String sql="SELECT\n" +
                "\tCEIL(COUNT(*)/?) as pagesize\n" +
                "FROM serviceorders\n" +
                "WHERE orderstate=0";
        //执行SQL语句
        return jdbcTemplate.queryForObject(sql,new Object[]{pageSize},Integer.class);
    }

    /**
     * 更新服务消息记录
     */
    @Override
    public int updateServiceRecord(String clientusername, String lon, String lat, String ordertime,String admin,String status) {
        String sqlUpdate="UPDATE serviceorders SET orderstate=?\n" +
                "\tWHERE clientusername=? AND TRIM(lon)=? AND TRIM(lat)=? AND ordertime=?";
        String sqlInsert="INSERT INTO servicedeals(orderid,adminaccount,endtime) VALUES(\n" +
                "\t(SELECT id FROM serviceorders\n" +
                "\tWHERE clientusername=? AND TRIM(lon)=? AND TRIM(lat)=? AND ordertime=?),\n" +
                "\t?,NOW())";
        //更新原始服务消息记录
        int update = jdbcTemplate.update(sqlUpdate, new Object[]{status,clientusername, lon, lat, ordertime});
        int update1 = jdbcTemplate.update(sqlInsert, new Object[]{clientusername, lon, lat, ordertime,admin});
        return update&update1;
    }

}
